use crate::server::Download;
use crate::{
    error::{AppErr, SqlxErr, UniqueMap},
    utils::current_timestamp,
};
use serde::Serialize;
use sqlx::Row;
use sqlx::{Executor, SqliteConnection};

use super::get_pool;

const CREATE_SQL: &'static str = r#"
    CREATE TABLE IF NOT EXISTS tb_firm_file (
        id INTEGER PRIMARY KEY AUTOINCREMENT, 
        type_id INTEGER NOT NULL, 
        name TEXT NOT NULL, 
        description TEXT NOT NULL, 
        version TEXT NOT NULL, 
        create_timestamp INTEGER NOT NULL, 
        size INTEGER NOT NULL, 
        body BLOB NOT NULL,
        UNIQUE (type_id, version)
    )
"#;

pub async fn init() -> Result<(), AppErr> {
    get_pool().execute(CREATE_SQL).await?;
    Ok(())
}

pub async fn download(id: i64) -> Result<Download, AppErr> {
    let row = sqlx::query("SELECT name, body FROM tb_firm_file WHERE id = ?")
        .bind(id)
        .fetch_optional(get_pool())
        .await?;
    if let Some(row) = row {
        let item = Download {
            name: row.get(0),
            body: row.get(1),
        };
        Ok(item)
    } else {
        Err(AppErr::from_static("没有对应的文件"))
    }
}

#[derive(Debug, Serialize)]
pub struct TbFirmFile {
    id: i64,
    name: String,
    description: String,
    version: String,
    size: i64,
    create_timestamp: i64,
}

pub async fn query(type_id: i64) -> Result<Box<[TbFirmFile]>, SqlxErr> {
    let rows = sqlx::query(r#"
        SELECT id, name, description, version, size, create_timestamp FROM tb_firm_file 
        WHERE type_id = ? ORDER BY id DESC
    "#).bind(type_id).fetch_all(get_pool()).await?;
    let mut vec = Vec::with_capacity(rows.len());
    for row in rows {
        let item = TbFirmFile {
            id: row.get(0),
            name: row.get(1),
            description: row.get(2),
            version: row.get(3),
            size: row.get(4),
            create_timestamp: row.get(5),
        };
        vec.push(item);
    }
    Ok(vec.into_boxed_slice())
}

pub async fn create(
    type_id: i64,
    name: &str,
    description: &str,
    version: &str,
    body: &[u8],
) -> Result<i64, AppErr> {
    let ret = sqlx::query("INSERT INTO tb_firm_file (type_id, name, description, version, create_timestamp, size, body) VALUES (?, ?, ?, ?, ?, ?, ?)")
        .bind(type_id)
        .bind(name)
        .bind(description)
        .bind(version)
        .bind(current_timestamp() as i64)
        .bind(body.len() as i64)
        .bind(body)
        .execute(get_pool())
        .await
        .map_unique("类型/版本重复")?;
    Ok(ret.last_insert_rowid())
}

pub async fn delete(id: i64) -> Result<(), SqlxErr> {
    sqlx::query("DELETE FROM tb_firm_file WHERE id = ?")
        .bind(id)
        .execute(get_pool())
        .await?;
    Ok(())
}

pub async fn delete_with_type(type_id: i64, conn: &mut SqliteConnection) -> Result<(), SqlxErr> {
    sqlx::query("DELETE FROM tb_firm_file WHERE type_id = ?")
        .bind(type_id)
        .execute(conn)
        .await?;
    Ok(())
}
